package com.question.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import com.question.dao.IPlayerDao;
import com.question.entity.Answer;
import com.question.entity.Question;
import com.question.entity.User;
import com.question.utils.DataSourceUtils;
import com.question.view.Configure;

public class PlayerDaoImpl implements IPlayerDao{
	@Override
	public List<Question> questionList(Question question) throws SQLException {
		List<Question> list = new ArrayList<>();
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDatasource());
		String sql = "SELECT q.id,question_title,question_info,credit,create_datetime,open_state,question_state_id,u.display_name" + 
				"	  FROM `question` q" + 
				"	  INNER JOIN t_user u" + 
				"	  ON q.t_user_id = u.id" + 
				"	  where question_state_id = ? or 0 = ?";
		List<Map<String,Object>> query = qr.query(sql, new MapListHandler(),question.getQuestion_state_id(),question.getQuestion_state_id());
		for (Map<String, Object> map : query) {
			Integer id = (Integer) map.get("id");
			String question_info = (String) map.get("question_info");
			String question_title  = (String) map.get("question_title");
			Date create_datetime = (Date) map.get("create_datetime");
			Integer open_state = (Integer) map.get("open_state");
			Integer question_state_id = (Integer) map.get("question_state_id");
			Integer credit = (Integer) map.get("credit");
			User user = new User();
			user.setDisplay_name((String) map.get("display_name"));
			list.add(new Question(id, question_info, create_datetime, credit, open_state, question_state_id, user,question_title));
		}
		return list;
	}

	@Override
	public Question getQuestionById(Integer id) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDatasource());
		String sql = "select * from question where id = ? and question_state_id = 1";
		return qr.query(sql,new BeanHandler<Question>(Question.class), id);
	}

	@Override
	public void answerQuestion(Answer answer) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDatasource());
		String sql = "insert into answer(answer_info,t_user_id,question_id)values(?,?,?)";
		qr.update(sql, answer.getAnswer_info(),answer.getUser().getId(),answer.getQuestion().getId());
	}

	@Override
	public void askQuestion(Question question) throws SQLException {
		QueryRunner qr = new QueryRunner();
		String sql = "insert into question (question_info,create_datetime,credit,open_state,question_state_id,t_user_id,question_title)"
				   + " values(?,?,?,?,?,?,?)";
		qr.update(DataSourceUtils.getConnection(),sql, question.getQuestion_info(),question.getCreate_datetime(),question.getCredit()
				,question.getOpen_state(),question.getQuestion_state_id(),question.getUser().getId(),question.getQuestion_title());
	}

	@Override
	public void editScoreByUser(User user) throws SQLException {
		QueryRunner qr =  new QueryRunner();
		String sql ="update t_user set score =  ?,ask_question = ask_question + 1  where id = ?";
		qr.update(DataSourceUtils.getConnection(),sql,user.getScore(),user.getId());
	}

	@Override
	public List<Question> findQuestionByUser(User player) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDatasource());
		String sql = "select * from question where t_user_id = ?";
		return	qr.query(sql, new BeanListHandler<Question>(Question.class),player.getId());
	}

	@Override
	public List<Answer> findAnswerByQuestionID(Integer inVal) throws SQLException {
		List<Answer> list = new ArrayList<>();
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDatasource());
		String sql = "select a.id,answer_info,display_name,answer_state from answer a inner join t_user u on a.t_user_id = u.id where a.question_id = ?";
		List<Map<String,Object>> query = qr.query(sql, new MapListHandler(), inVal);
		Question(list, query);
		return list;
	}
	@Override
	public List<Answer> findAnswerByQuestionIDAndPlayer(User player, Integer qId) throws SQLException {
		List<Answer> list = new ArrayList<>();
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDatasource());
		String sql = "SELECT" + 
				"	a.id," + 
				"	answer_info," + 
				"	display_name," + 
				"	answer_state " + 
				" FROM" + 
				"	t_user u\r\n" + 
				"	INNER JOIN answer a ON u.id = a.t_user_id " + 
				"	INNER JOIN question q ON a.question_id = q.id" + 
				" WHERE" + 
				"	q.t_user_id = ? " + 
				"	AND q.id = ?";
		List<Map<String,Object>> query = qr.query(sql, new MapListHandler(),player.getId(),qId);
		Question(list, query);
		return list;
	}

	@Override
	public User findUserByAnswerId(Integer id) throws SQLException {
		 QueryRunner qr = new QueryRunner();
		 String sql = "select score,u.id,answer from answer a,t_user u where a.t_user_id = u.id and a.id = ?";
		 return qr.query(DataSourceUtils.getConnection(),sql, new BeanHandler<User>(User.class), id);
	}

	@Override
	public void updateUserAccept(User user) throws SQLException {
		QueryRunner qr = new QueryRunner();
		String sql = "update t_user set score = ?,answer=? where id =?";
	    qr.update(DataSourceUtils.getConnection(), sql,user.getScore(),user.getAnswer(),user.getId());
	}

	@Override
	public void updateQuestionAccept(Question question) throws SQLException {
		QueryRunner qr = new QueryRunner();
		String sql = "update question set question_state_id = ? where id = ?";
		qr.update(DataSourceUtils.getConnection(), sql,Question.QUESTION_STATE_TYPE2,question.getId());
		
	}

	@Override
	public void updateAnswerAccept(Integer id) throws SQLException {
		QueryRunner qr = new QueryRunner();
		String sql = "update answer set answer_state = ? where id = ?";
		qr.update(DataSourceUtils.getConnection(), sql,1,id);
	}

	@Override
	public User updatePlayer(User player) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDatasource());
		String sql = "select * from t_user where id = ?";
		return qr.query(sql, new BeanHandler<User>(User.class),player.getId());
	}

	@Override
	public List<User> rankUserByScoreTop10() throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDatasource());
		String sql = "select display_name,score,answer from t_user order by score desc,answer desc limit 0,?";
		return qr.query(sql, new BeanListHandler<User>(User.class),Configure.RANKNUMBER);
		
	}
	private void Question(List<Answer> list, List<Map<String, Object>> query) {
		for (Map<String, Object> map : query) {
			Integer id =  (Integer) map.get("id");
			String answer_info = (String) map.get("answer_info");
			String display_name = (String) map.get("display_name");
			Integer answer_state = (Integer) map.get("answer_state");
			User user = new User();
			user.setDisplay_name(display_name);
			if(list.size()!=0 && answer_state == 1 ) {
				Answer tmp = list.get(0);
				list.set(0, new Answer(id, answer_info, user, answer_state));
				list.add(tmp);
			}else {
				list.add(new Answer(id, answer_info, user, answer_state));
			}
		}
	}

	@Override
	public com.question.entity.Question findQuestionInfoById(Integer id) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDatasource());
		String sql = "select * from question where id = ? ";
		return qr.query(sql,new BeanHandler<Question>(Question.class), id);
	}
}
